﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Globalization;
using System.Linq;

namespace LiveResults
{
    /// <summary>
    /// The OLA parser connects directly to the OLA database and monitors changes in results and splittimes, this is done in a polling thread 
    /// that each second checks database for updated records since last received update.
    /// 
    /// It can handle Most OLA-connection including the new H2-connection in OLA5
    /// </summary>
    public class OlaParser : BaseParser
    {
        public enum OLA_CONNECTION_TYPE { OLA5_INTERNAL_DB, OLA5_MYSQL, OLA4_MYSQL, OLA5_SQLSERVER };

        public class OlaListItem
        {
            public OlaListItem(OLA_CONNECTION_TYPE type)
            {
                ConnectionType = type;
            }
            public OLA_CONNECTION_TYPE ConnectionType;
            public override string ToString()
            {
                return GetDisplayName(ConnectionType);
            }
        }

        /// <summary>
        /// Get a display-name for the specific ConnectionType
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string GetDisplayName(OLA_CONNECTION_TYPE type)
        {
            switch (type)
            {
                case OLA_CONNECTION_TYPE.OLA5_INTERNAL_DB:
                    return "OLA5 Internal DB";
                case OLA_CONNECTION_TYPE.OLA5_MYSQL:
                    return "OLA5 MySQL";
                case OLA_CONNECTION_TYPE.OLA4_MYSQL:
                    return "OLA4 MySQL";
                case OLA_CONNECTION_TYPE.OLA5_SQLSERVER:
                    return "OLA SQL-Server";
            }
            return "";
        }

        private IDbConnection m_Connection;
        private int m_EventID;
        private int m_EventRaceId;

        private bool m_Continue = false;
        OLA_CONNECTION_TYPE m_ConnType;
        public OlaParser(IDbConnection conn, int eventID, int eventRaceId, OLA_CONNECTION_TYPE connType)
        {
            m_Connection = conn;
            m_EventID = eventID;
            m_EventRaceId = eventRaceId;
            m_ConnType = connType;
        }

        System.Threading.Thread th;
        public void Start()
        {
            m_Continue = true;
            if (m_Connection.State != ConnectionState.Open)
                m_Connection.Open();

            th = new System.Threading.Thread(new System.Threading.ThreadStart(run));
            th.Start();
        }

        public void Stop()
        {
            m_Continue = false;
            m_Connection.Close();
        }

        private void run()
        {
            while (m_Continue)
            {
                try
                {
                    string paramOper = "?";
                    if (m_ConnType == OLA_CONNECTION_TYPE.OLA4_MYSQL || m_ConnType == OLA_CONNECTION_TYPE.OLA5_MYSQL)
                    {
                        paramOper = "?date";
                    }


                    /*Detect eventtype*/
                    string scmd = "select eventForm from events where eventid = " + m_EventID;
                    IDbCommand cmd = m_Connection.CreateCommand();
                    cmd.CommandText = scmd;

                    string form = cmd.ExecuteScalar() as string;
                    bool isRelay = false;

                    if (form == "relay")
                        isRelay = true;


                    
                    string baseCommand; 
                    string splitbaseCommand;
                    
                    ///In OLA5 the Club-table is replaced by the Organisation-table
                    if (m_ConnType == OLA_CONNECTION_TYPE.OLA5_MYSQL || m_ConnType == OLA_CONNECTION_TYPE.OLA5_INTERNAL_DB || m_ConnType == OLA_CONNECTION_TYPE.OLA5_SQLSERVER)
                    {
                        baseCommand = "select results.modifyDate, results.totalTime, results.position, persons.familyname as lastname, persons.firstname as firstname, organisations.name as clubname, eventclasses.shortName, results.runnerStatus, results.entryid from results, entries, Persons, organisations, raceclasses,eventclasses where raceclasses.eventClassID = eventClasses.eventClassID and results.raceClassID = raceclasses.raceclassid and raceClasses.eventRaceId = " + m_EventRaceId + " and eventclasses.eventid = " + m_EventID + " and results.entryid = entries.entryid and entries.competitorid = persons.personid and persons.defaultorganisationid = organisations.organisationid and results.runnerStatus != 'notActivated' and results.modifyDate > " + paramOper;
                        splitbaseCommand = "select splittimes.modifyDate, splittimes.passedTime, Controls.ID, results.entryid, results.allocatedStartTime, results.starttime, persons.familyname as lastname, persons.firstname as firstname, organisations.name as clubname, eventclasses.shortName, splittimes.passedCount from splittimes, results, SplitTimeControls, Controls, eventClasses, raceClasses, Persons, organisations, entries where splittimes.resultraceindividualnumber = results.resultid and SplitTimes.splitTimeControlID = SplitTimeControls.splitTimeControlID and SplitTimeControls.timingControl = Controls.controlid and Controls.eventRaceId = " + m_EventRaceId + " and raceclasses.eventClassID = eventClasses.eventClassID and results.raceClassID = raceclasses.raceclassid and raceClasses.eventRaceId = " + m_EventRaceId + " and eventclasses.eventid = " + m_EventID + " and results.entryid = entries.entryid and entries.competitorid = persons.personid and persons.defaultorganisationid = organisations.organisationid and splitTimes.modifyDate > " + paramOper;
                    }
                    else 
                    {
                        baseCommand = "select results.modifyDate, results.totalTime, results.position, persons.familyname as lastname, persons.firstname as firstname, clubs.name as clubname, eventclasses.shortName, results.runnerStatus, results.entryid from results, entries, Persons, Clubs, raceclasses,eventclasses where raceclasses.eventClassID = eventClasses.eventClassID and results.raceClassID = raceclasses.raceclassid and raceClasses.eventRaceId = " + m_EventRaceId + " and eventclasses.eventid = " + m_EventID + " and results.entryid = entries.entryid and entries.competitorid = persons.personid and persons.clubid = clubs.clubid and results.runnerStatus != 'notActivated' and results.modifyDate > " + paramOper;
                        splitbaseCommand = "select splittimes.modifyDate, splittimes.passedTime, Controls.ID, results.entryid, results.allocatedStartTime, results.starttime, persons.familyname as lastname, persons.firstname as firstname, clubs.name as clubname, eventclasses.shortName, splittimes.passedCount from splittimes, results, SplitTimeControls, Controls, eventClasses, raceClasses, Persons, Clubs, entries where splittimes.resultraceindividualnumber = results.resultid and SplitTimes.splitTimeControlID = SplitTimeControls.splitTimeControlID and SplitTimeControls.timingControl = Controls.controlid and Controls.eventRaceId = " + m_EventRaceId + " and raceclasses.eventClassID = eventClasses.eventClassID and results.raceClassID = raceclasses.raceclassid and raceClasses.eventRaceId = " + m_EventRaceId + " and eventclasses.eventid = " + m_EventID + " and results.entryid = entries.entryid and entries.competitorid = persons.personid and persons.clubid = clubs.clubid and splitTimes.modifyDate > " + paramOper;
                    }

                    if (isRelay)
                    {
                        baseCommand = "select results.modifyDate,results.totalTime, results.position, persons.familyname as lastname, persons.firstname as firstname, entries.teamName as clubname, eventclasses.shortName, raceclasses.relayleg, results.runnerStatus, results.relayPersonId as entryId, results.finishTime, results.allocatedStartTime from results, entries, Persons, Clubs, raceclasses,eventclasses where raceclasses.eventClassID = eventClasses.eventClassID and results.raceClassID = raceclasses.raceclassid and raceClasses.eventRaceId = " + m_EventRaceId + " and eventclasses.eventid = " + m_EventID + " and results.entryid = entries.entryid and results.relaypersonid = persons.personid and persons.clubid = clubs.clubid and results.runnerStatus != 'notActivated' and results.modifyDate > " + paramOper;
                        splitbaseCommand = "select splittimes.modifyDate, splittimes.passedTime, Controls.ID, results.relayPersonId as entryId, results.allocatedStartTime, persons.familyname as lastname, persons.firstname as firstname, entries.teamName as clubname, eventclasses.shortName,raceclasses.relayleg, splittimes.passedCount,results.allocatedStartTime  from splittimes, results, SplitTimeControls, Controls, eventClasses, raceClasses, Persons, Clubs, entries where splittimes.resultraceindividualnumber = results.resultid and SplitTimes.splitTimeControlID = SplitTimeControls.splitTimeControlID and SplitTimeControls.timingControl = Controls.controlid and Controls.eventRaceId = " + m_EventRaceId + " and raceclasses.eventClassID = eventClasses.eventClassID and results.raceClassID = raceclasses.raceclassid and raceClasses.eventRaceId = " + m_EventRaceId + " and eventclasses.eventid = " + m_EventID + " and results.entryid = entries.entryid and results.relaypersonid = persons.personid and persons.clubid = clubs.clubid and splitTimes.modifyDate > " + paramOper;
                    }
                    
                    cmd.CommandText = baseCommand;
                    IDbCommand cmdSplits = m_Connection.CreateCommand();
                    cmdSplits.CommandText = splitbaseCommand;

                    IDbDataParameter param = CreateModifyDateParam(cmd);
                    IDbDataParameter splitparam = CreateModifyDateParam(cmdSplits);

                    DateTime lastDateTime = DateTime.Now.AddMonths(-120);
                    DateTime lastSplitDateTime = DateTime.Now.AddMonths(-120);
                    cmd.Parameters.Add(param);
                    cmdSplits.Parameters.Add(splitparam);
                    IDataReader reader = null;


                    /*Load classes and splittimecontrols*/
                    if (m_ConnType == OLA_CONNECTION_TYPE.OLA5_MYSQL || m_ConnType == OLA_CONNECTION_TYPE.OLA5_INTERNAL_DB || m_ConnType == OLA_CONNECTION_TYPE.OLA5_SQLSERVER)
                    {
                        IDbCommand cmdClasses = m_Connection.CreateCommand();
                        cmdClasses.CommandText = "select ec.ShortName, rc.modifyDate,c.courseLength, ec.sequence, bc.sequence as basesequence from raceClasses rc, eventClasses ec, raceClassCourses rcc, courses c, baseclasses bc where ec.eventClassID = rc.EventClassID and rcc.raceClassId= rc.RaceClassID and bc.classid=ec.baseclassid and rcc.courseid = c.courseid and rc.eventRaceId = " + m_EventRaceId + " and ec.eventid = " + m_EventID;
                        reader = cmdClasses.ExecuteReader();

                        var sc = new { code = 0, name = "", order = 0 };
                        var o = new { shortName ="", courseLength=0, order = 0, splitControls = MakeList(sc)};
                        var classes = MakeDictionary(o);
                        while (reader.Read())
                        {
                            try
                            {
                                string shortName = reader["shortname"] as string;
                                int courseLength = 0;
                                int.TryParse(reader["COURSELENGTH"].ToString(), out courseLength);
                                int order = 0;
                                if (reader["sequence"] != null && reader["sequence"] != DBNull.Value)
                                {
                                    int.TryParse(reader["sequence"].ToString(), out order);
                                }
                                else if (reader["basesequence"] != null && reader["basesequence"] != DBNull.Value)
                                {
                                    int.TryParse(reader["basesequence"].ToString(), out order);
                                }
                                //FireOnClass(shortName, courseLength, 0, order);
                                classes.Add(shortName, new { shortName = shortName, courseLength = courseLength, order = order, splitControls = MakeList(sc) });
                            }
                            catch (Exception ee)
                            {
                                FireLogMsg("Error parsing class: " + ee.Message);
                            }
                        }
                        reader.Close();

                        cmdClasses.CommandText = @"select stc.name, rstc.ordered, ec.shortName, c.ID from raceClassSplitTimeControls rstc, splittimecontrols stc, raceclasses rc, eventclasses ec, controls c where 
                                                    rstc.splittimecontrolid = stc.splittimecontrolid and rc.eventclassid = ec.eventclassid and rstc.raceclassid= rc.raceclassid and stc.timingcontrol = c.controlid and rc.eventRaceId = " + m_EventRaceId + " and ec.eventid = " + m_EventID;

                        reader = cmdClasses.ExecuteReader();
                        while (reader.Read())
                        {
                            string shortName = reader["shortName"] as string;
                            string controlName = reader["name"] as string;
                            int controlID = Convert.ToInt32(reader["ID"].ToString());
                            int order = Convert.ToInt32(reader["ordered"].ToString());
                            if (classes.ContainsKey(shortName))
                            {
                                classes[shortName].splitControls.Add(new { code = controlID, name = controlName, order = order });
                            }
                        }
                        reader.Close();

                        foreach (var c in classes)
                        {
                            List<ControlStruct> str = c.Value.splitControls.OrderBy(x => x.order).Select(x => new ControlStruct() { Name = x.name, Code = x.code }).ToList();

                            Dictionary<int, int> usedNumbers = new Dictionary<int, int>();
                            for (int i = 0; i < str.Count; i++)
                            {
                                ControlStruct cc = str[i];
                                if (usedNumbers.ContainsKey(cc.Code))
                                {
                                    usedNumbers[cc.Code]++;
                                    cc.Code = (usedNumbers[cc.Code]) * 1000 + cc.Code;
                                }
                                else
                                {
                                    usedNumbers.Add(cc.Code, 1);
                                    cc.Code = 1000 + cc.Code;
                                }
                                str[i] = cc;
                            }

                            FireOnClass(c.Value.shortName, c.Value.courseLength, 0, c.Value.order, str);
                        }
                    }


                    FireLogMsg("OLA Monitor thread started");
                    while (m_Continue)
                    {
                        
                        string lastRunner = "";
                        try
                        {
                            /* Set last seen timestamp*/
                            if (m_ConnType == OLA_CONNECTION_TYPE.OLA4_MYSQL || m_ConnType == OLA_CONNECTION_TYPE.OLA5_INTERNAL_DB || m_ConnType == OLA_CONNECTION_TYPE.OLA5_MYSQL)
                            {
                                (cmd.Parameters["date"] as IDbDataParameter).Value = lastDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff");
                                (cmdSplits.Parameters["date"] as IDbDataParameter).Value = lastSplitDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff");
                            }
                            else
                            {
                                (cmd.Parameters["@date"] as IDbDataParameter).Value = lastDateTime;
                                (cmdSplits.Parameters["@date"] as IDbDataParameter).Value = lastSplitDateTime;
                            }

                            /*Kontrollera om nya resultat*/

                            reader = cmd.ExecuteReader();
                            while (reader.Read())
                            {
                                DateTime modDate = DateTime.MinValue;
                                int time = 0, position = 0, runnerID = 0;
                                string famName = "", fName = "", club = "", classN = "", status = "";
                                try
                                {
                                    string sModDate = Convert.ToString(reader[0]);
                                    modDate = ParseDateTime(sModDate);

                                    lastDateTime = (modDate > lastDateTime ? modDate : lastDateTime);
                                    runnerID = Convert.ToInt32(reader["entryid"].ToString());
                                    
                                    time = -9;
                                    if (reader["totaltime"] != null && reader["totaltime"] != DBNull.Value)
                                        time = Convert.ToInt32(reader["totalTime"].ToString());

                                    famName = reader["lastname"] as string;
                                    fName = reader["firstname"] as string;
                                    lastRunner = (string.IsNullOrEmpty(fName) ? "" : (fName + " ")) + famName;
                                    
                                    club = reader["clubname"] as string;
                                    classN = reader["shortname"] as string;
                                    status = reader["runnerStatus"] as string;

                                    if (isRelay)
                                    {
                                        classN = classN + "-" + Convert.ToString(reader["relayLeg"]);
                                        if (reader["finishTime"] != DBNull.Value)
                                        {
                                            //DateTime ft = Convert.ToDateTime(reader["finishTime"]);
                                            //DateTime ast = Convert.ToDateTime(reader["allocatedStartTime"]);
                                            
                                            //time = (int)(((TimeSpan)(ft - ast)).TotalSeconds * 100);
                                        }
                                    }
                                    
                                }
                                catch (Exception ee)
                                {
                                    FireLogMsg(ee.Message);
                                }


                                /*
                                    time is seconds * 100
                                 * 
                                 * valid status is
                                    notStarted
                                    finishedTimeOk
                                    finishedPunchOk
                                    disqualified
                                    finished
                                    movedUp
                                    walkOver
                                    started
                                    passed
                                    notValid
                                    notActivated
                                    notParticipating
                                 */
                                //EMMAClient.RunnerStatus rstatus = EMMAClient.RunnerStatus.Passed;
                                RunnerStatus rstatus = RunnerStatus.OK;
                                switch (status)
                                {
                                    case "started":
                                        rstatus = RunnerStatus.STARTED;
                                        break;
                                    case "notActivated":
                                        rstatus = RunnerStatus.NOT_ACTIVATED;
                                        //rstatus = EMMAClient.RunnerStatus.NotStartedYet;
                                        break;
                                    case "notStarted":
                                        rstatus = RunnerStatus.DID_NOT_START;
                                        //rstatus = EMMAClient.RunnerStatus.NotStarted;
                                        break;
                                    case "disqualified":
                                        rstatus = RunnerStatus.DISQUALIFIED;
                                        break;
                                    case "notValid":
                                        rstatus = RunnerStatus.MISSING_PUNCH;
                                        //rstatus = EMMAClient.RunnerStatus.MissingPunch;
                                        break;
                                    case "notParticipating":
                                    case "walkOver":
                                        rstatus = RunnerStatus.WALK_OVER;
                                        //rstatus = EMMAClient.RunnerStatus.WalkOver;
                                        break;
                                    case "movedUp":
                                        rstatus = RunnerStatus.MOVED_UP;
                                        break;
                                }
                                if (rstatus != RunnerStatus.STARTED && rstatus != RunnerStatus.NOT_ACTIVATED)
                                {
                                    FireOnResult(runnerID, 0, fName, famName, club, classN, 0, time, rstatus, new List<ResultStruct>());
                                }

                            }
                            reader.Close();

                            reader = cmdSplits.ExecuteReader();
                            while (reader.Read())
                            {
                                string smod = Convert.ToString(reader[0]);
                                DateTime mod;
                                mod = ParseDateTime(smod);

                                lastSplitDateTime = (mod > lastSplitDateTime ? mod : lastSplitDateTime);
                                string tTime = Convert.ToString(reader[1]);
                                DateTime pTime ;
                                pTime = ParseDateTime(tTime);
                                int sCont = reader.GetInt32(2);
                                int entryid = Convert.ToInt32(reader["entryid"].ToString());
                                DateTime startTime ;
                                if (reader["allocatedStartTime"] != null && reader["allocatedStartTime"] != DBNull.Value)
                                {
                                    tTime = reader["allocatedStartTime"].ToString();
                                    startTime = ParseDateTime(tTime);
                                }
                                else if (reader["starttime"] != null && reader["starttime"] != DBNull.Value)
                                {
                                    tTime = reader["starttime"].ToString();
                                    startTime = ParseDateTime(tTime);
                                }
                                else
                                {
                                    continue;
                                }
                                int passedCount = Convert.ToInt32(reader["passedCount"].ToString());
                                TimeSpan rTid = pTime - startTime;
                                double time = rTid.TotalMilliseconds / 10;
                                List<ResultStruct> times = new List<ResultStruct>();
                                ResultStruct t = new ResultStruct();
                                t.ControlCode = sCont + 1000*passedCount;
                                t.ControlNo = 0;
                                t.Time = (int)time;
                                times.Add(t);

                                string sfamName = reader["lastname"] as string;
                                string sfName = reader["firstname"] as string;
                                string name = (string.IsNullOrEmpty(sfName) ? "" : (sfName + " ")) + sfamName;

                                string club = reader["clubname"] as string;
                                string classn = reader["shortname"] as string;

                                if (isRelay)
                                {
                                    classn = classn + "-" + Convert.ToString(reader["relayLeg"]);
                                }

                                FireOnResult(entryid, 0, sfName, sfamName, club, classn, 0, -2, RunnerStatus.OK, times);
                            }
                            reader.Close();

                            System.Threading.Thread.Sleep(1000);
                        }
                        catch (Exception ee)
                        {
                            if (reader != null)
                                reader.Close();
                            FireLogMsg("OLA Parser: " + ee.Message + " {parsing: " + lastRunner);

                            switch (m_Connection.State)
                            {
                                case ConnectionState.Broken:
                                case ConnectionState.Closed:
                                    m_Connection.Close();
                                    m_Connection.Open();
                                    break;
                            }
                            System.Threading.Thread.Sleep(1000);
                        }
                    }
                }
                catch (Exception ee)
                {
                    FireLogMsg("OLA Parser: " +ee.Message);
                    System.Threading.Thread.Sleep(1000);
                }
                finally
                {
                    if (m_Connection != null)
                    {
                        m_Connection.Close();
                    }
                    FireLogMsg("Disconnected");
                    FireLogMsg("OLA Monitor thread stopped");

                }
            }
        }

        private IDbDataParameter CreateModifyDateParam(IDbCommand cmd)
        {
            IDbDataParameter param = cmd.CreateParameter();
            param.ParameterName = "date";

            if (m_ConnType == OLA_CONNECTION_TYPE.OLA4_MYSQL || m_ConnType == OLA_CONNECTION_TYPE.OLA5_INTERNAL_DB || m_ConnType == OLA_CONNECTION_TYPE.OLA5_MYSQL)
            {
                param.DbType = DbType.String;
                param.Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }
            else
            {
                param.ParameterName = "@date";
                param.DbType = DbType.DateTime;
                param.Value = DateTime.Now;
            }
            return param;
        }

        /// <summary>
        /// Trying to parse a datetime that can contain arbitrary fractions of seconds...
        /// </summary>
        /// <param name="tTime"></param>
        /// <returns></returns>
        private static DateTime ParseDateTime(string tTime)
        {
            DateTime startTime;
            if (!DateTime.TryParseExact(tTime, new string[] {"yyyy-MM-dd HH:mm:ss","yyyy-MM-dd HH:mm:ss.f","yyyy-MM-dd HH:mm:ss.ff","yyyy-MM-dd HH:mm:ss.fff"}, CultureInfo.InvariantCulture, DateTimeStyles.None, out startTime))
            {
            }
            return startTime;
        }

        public static Dictionary<string,T> MakeDictionary<T>(T itemOftype)
        {
            Dictionary<string,T> newList = new Dictionary<string,T>();
            return newList;
        }

        public static List<T> MakeList<T>(T itemOftype)
        {
            List<T> newList = new List<T>();
            return newList;
        }
    }
}
